package test;

import java.io.ObjectInputStream.GetField;
import java.util.HashMap;
import java.util.List;

import util.ExcelTools;
import util.FileTools;

public class ReadExcel {
	private static HashMap columnMap = new HashMap();
	private static HashMap nameMap = new HashMap();
	private static HashMap tableNameMap = new HashMap();// 用于JDBC查询历史库时,拼SQL
	private static final int STRING = 0;
	private static final int INTEGER = 1;
	private static final int DATE = 2;

	public ReadExcel() {
		columnMap.put("asbNmsEmsName", STRING);
		nameMap.put("网管名称", "asbNmsEmsName");
		columnMap.put("asbInventAlertKey", STRING);
		nameMap.put("资源告警位置", "asbInventAlertKey");
		columnMap.put("asbInventNode", STRING);
		nameMap.put("基站", "asbInventNode");
		columnMap.put("asbEmsEventTime", STRING);
		nameMap.put("设备告警最后时间", "asbEmsEventTime");
		columnMap.put("asbAdditionalInfo", STRING);
		nameMap.put("N/A", "asbAdditionalInfo");
		columnMap.put("asbSleepUntil", DATE);
		nameMap.put("N/A", "asbSleepUntil");
		columnMap.put("asbAssociationServices", INTEGER);
		nameMap.put("N/A", "asbAssociationServices");
		columnMap.put("asbAssociationTTS", INTEGER);
		nameMap.put("N/A", "asbAssociationTTS");
		columnMap.put("asbNeId", STRING);
		nameMap.put("N/A", "asbNeId");
		columnMap.put("asbPortId", STRING);
		nameMap.put("N/A", "asbPortId");
		columnMap.put("asbCircuitId", STRING);
		nameMap.put("N/A", "asbCircuitId");
		columnMap.put("asbPossibleRc", INTEGER);
		nameMap.put("是否根告警", "asbPossibleRc");
		columnMap.put("asbAssociationId", INTEGER);
		nameMap.put("N/A", "asbAssociationId");
		columnMap.put("asbLastClearedAt", DATE);
		nameMap.put("N/A", "asbLastClearedAt");
		columnMap.put("asbLocationExchange", STRING);
		nameMap.put("局站", "asbLocationExchange");
		columnMap.put("asbLocationRoom", STRING);
		nameMap.put("机房名称", "asbLocationRoom");
		columnMap.put("asbLocationDistrict", STRING);
		nameMap.put("N/A", "asbLocationDistrict");
		columnMap.put("asb_location_city", STRING);
		nameMap.put("所属本地网", "asb_location_city");
		columnMap.put("asbLocation", STRING);
		nameMap.put("N/A", "asbLocation");
		columnMap.put("asbCustomerId", STRING);
		nameMap.put("大客户编号", "asbCustomerId");
		columnMap.put("asbServiceSla", INTEGER);
		nameMap.put("N/A", "asbServiceSla");
		columnMap.put("asbServiceId", STRING);
		nameMap.put("电路名称", "asbServiceId");
		columnMap.put("asbServiceType", STRING);
		nameMap.put("N/A", "asbServiceType");
		columnMap.put("asbServiceSimpacted", INTEGER);
		nameMap.put("N/A", "asbServiceSimpacted");
		columnMap.put("asbAlertType", STRING);
		nameMap.put("告警子类型", "asbAlertType");
		columnMap.put("asbTTID", STRING);
		nameMap.put("工单号", "asbTTID");
		columnMap.put("asbTTCloseTime", DATE);
		nameMap.put("N/A", "asbTTCloseTime");
		columnMap.put("asbTTOpenTime", DATE);
		nameMap.put("派单时间", "asbTTOpenTime");
		columnMap.put("asbAcknowledgedBy", STRING);
		nameMap.put("N/A", "asbAcknowledgedBy");
		columnMap.put("asbFirstAcknowledged", DATE);
		nameMap.put("确认时间", "asbFirstAcknowledged");
		columnMap.put("asbAlarmState", INTEGER);
		nameMap.put("工单状态", "asbAlarmState");
		columnMap.put("asbImpactStatus", INTEGER);
		nameMap.put("N/A", "asbImpactStatus");
		// columnMap.put("asbOrigSeverity",INTEGER);
		// nameMap.put("告警级别","asbOrigSeverity");
		columnMap.put("asbAlarmSeq", STRING);
		nameMap.put("N/A", "asbAlarmSeq");
		columnMap.put("asbCustomerLevel", INTEGER);
		nameMap.put("N/A", "asbCustomerLevel");
		columnMap.put("asbTechDomain", INTEGER);
		nameMap.put("专业", "asbTechDomain");
		columnMap.put("asbEquipModel", STRING);
		nameMap.put("N/A", "asbEquipModel");
		columnMap.put("asbEquipManufacturer", STRING);
		nameMap.put("N/A", "asbEquipManufacturer");
		columnMap.put("serverSerial", INTEGER);
		nameMap.put("N/A", "serverSerial");
		columnMap.put("serverName", STRING);
		nameMap.put("N/A", "serverName");
		// columnMap.put("originalSeverity", INTEGER);
		// nameMap.put("N/A","originalSeverity");
		columnMap.put("deletedAt", DATE);
		nameMap.put("N/A", "deletedAt");
		columnMap.put("x733corrnotif", STRING);
		nameMap.put("解决方案", "x733corrnotif");
		columnMap.put("x733SpecificProb", STRING);
		//nameMap.put("告警原因", "x733SpecificProb");
		nameMap.put("告警名称", "x733SpecificProb");
		columnMap.put("x733ProbableCause", INTEGER);
		nameMap.put("N/A", "x733ProbableCause");
		columnMap.put("x733EventType", INTEGER);
		nameMap.put("N/A", "x733EventType");
		columnMap.put("taskList", INTEGER);
		nameMap.put("N/A", "taskList");
		columnMap.put("service", STRING);
		nameMap.put("N/A", "service");
		columnMap.put("customer", STRING);
		nameMap.put("大客户", "customer");
		columnMap.put("suppressescl", INTEGER);
		nameMap.put("N/A", "suppressescl");
		columnMap.put("expireTime", INTEGER);
		nameMap.put("N/A", "expireTime");
		columnMap.put("flash", INTEGER);
		nameMap.put("N/A", "flash");
		columnMap.put("acknowledged", INTEGER);
		nameMap.put("确认状态", "acknowledged");
		columnMap.put("ownerGid", INTEGER);
		nameMap.put("区域", "ownerGid");
		columnMap.put("ownerUid", INTEGER);
		nameMap.put("N/A", "ownerUid");
		columnMap.put("location", STRING);
		nameMap.put("N/A", "location");
		columnMap.put("grade", INTEGER);
		nameMap.put("N/A", "grade");
		columnMap.put("clazz", INTEGER);
		nameMap.put("N/A", "clazz");
		columnMap.put("tally", INTEGER);
		nameMap.put("次数", "tally");
		columnMap.put("type", INTEGER);
		nameMap.put("N/A", "type");
		columnMap.put("poll", INTEGER);
		nameMap.put("N/A", "poll");
		columnMap.put("IntegerernalLast", DATE);
		nameMap.put("N/A", "IntegerernalLast");
		columnMap.put("lastOccurrence", DATE);
		nameMap.put("综合告警最后时间", "lastOccurrence");
		columnMap.put("firstOccurrence", DATE);
		//nameMap.put("首次发生时间", "firstOccurrence");
		nameMap.put("综合告警首次时间", "firstOccurrence");
		columnMap.put("stateChange", DATE);
		nameMap.put("N/A", "stateChange");
		// columnMap.put("severity", INTEGER); nameMap.put("级别变动情况","severity");
		columnMap.put("severity", INTEGER);
		//nameMap.put("告警级别", "severity");
		nameMap.put("清除状态", "severity");
		columnMap.put("serial", INTEGER);
		nameMap.put("N/A", "serial");
		columnMap.put("summary", STRING);
		nameMap.put("告警摘要", "summary");
		columnMap.put("alertKey", STRING);
		nameMap.put("告警关键字", "alertKey");
		columnMap.put("alertGroup", STRING);
		nameMap.put("告警类型", "alertGroup");
		columnMap.put("agent", STRING);
		nameMap.put("N/A", "agent");
		columnMap.put("manager", STRING);
		nameMap.put("N/A", "manager");
		//columnMap.put("nodeAlias", STRING);
		columnMap.put("asb_netype", STRING);
		nameMap.put("网元类型", "asb_netype");
		columnMap.put("node", STRING);
		nameMap.put("节点", "node");
		columnMap.put("identifier", STRING);
		nameMap.put("N/A", "identifier");
		columnMap.put("asbNmsemsAdapterId", STRING);
		nameMap.put("网管", "asbNmsemsAdapterId");
		tableNameMap.put("identifier","identifier");
	    tableNameMap.put("node","node");
		tableNameMap.put("alertGroup","alertgroup");
	    tableNameMap.put("asbAlertType","asb_alerttype");
	    tableNameMap.put("alertKey","alertkey");
	    tableNameMap.put("severity","severity");
	    tableNameMap.put("summary","summary");
	    tableNameMap.put("firstOccurrence","firstOccurrence");
	    tableNameMap.put("lastOccurrence","lastOccurrence");
	    tableNameMap.put("asbAlarmState","asb_alarmstate");
	    tableNameMap.put("tally","tally");
	    tableNameMap.put("customer","customer");
	    tableNameMap.put("asbCustomerId","asb_customerid");
	    tableNameMap.put("asbTechDomain","asb_tech_domain");
	    tableNameMap.put("asbOrigSeverity","asb_origseverity");
	    tableNameMap.put("asbFirstAcknowledged","asb_firstAcknowledged");
	    tableNameMap.put("asbTTOpenTime","asb_ttopentime"); 
	    tableNameMap.put("asbPossibleRc","asb_possiblerc");  
	    tableNameMap.put("asbEmsEventTime","asb_emseventtime");
	    tableNameMap.put("asbInventNode","asb_inventnode");
	    tableNameMap.put("asbInventAlertKey","asb_inventalertkey");
	    tableNameMap.put("asbNmsEmsName","asb_nmsemsname");
	    tableNameMap.put("asbServiceId","asb_serviceid");
	    tableNameMap.put("asbLocationRoom","asb_location_room");
	    tableNameMap.put("asbLocationExchange","asb_location_exchange");
	    tableNameMap.put("ownergid","ownergid");
	    tableNameMap.put("asb_location_city","asb_location_city");
	    tableNameMap.put("asbNmsemsAdapterId","asb_nmsems_adapter_id");
	    tableNameMap.put("acknowledged","Acknowledged");
	    tableNameMap.put("asb_netype","asb_netype");
	    tableNameMap.put("asbTTID","ASB_TTID");
	    tableNameMap.put("x733SpecificProb","X733SpecificProb");
	    tableNameMap.put("x733corrnotif","x733corrnotif");
	    tableNameMap.put("type","type");
	    
	    nameMap.put("设备告警恢复时间","asbEmsResolvedTime");
	    nameMap.put("网元编码","alarmId");
	    nameMap.put("基站站址","location");
	    nameMap.put("扇区数","asbCustomerFlag");
	    nameMap.put("基站级别","asbSummary1");
	    nameMap.put("蜂窝类型","asbSummary3");
	    nameMap.put("所属BSC","asbSummary8");
	    nameMap.put("所属BTS","asbSummary9");
	    nameMap.put("告警级别","asbSummary4");
	    columnMap.put("asbEmsResolvedTime",STRING);
	    columnMap.put("alarmId",STRING);
	    columnMap.put("location",STRING);
	    columnMap.put("asbCustomerFlag",INTEGER);
	    columnMap.put("asbSummary1",STRING);
	    columnMap.put("asbSummary3",STRING);
	    columnMap.put("asbSummary8",STRING);
	    columnMap.put("asbSummary9",STRING);
	    columnMap.put("asbSummary4",STRING);
	    tableNameMap.put("asbEmsResolvedTime","ASB_EMSRESOLVEDTIME");
	    tableNameMap.put("alarmId","ALARMID");
	    tableNameMap.put("location","LOCATION");
	    tableNameMap.put("asbCustomerFlag","ASB_CUSTOMERFLAG");
	    tableNameMap.put("asbSummary1","ASB_SUMMARY1");
	    tableNameMap.put("asbSummary3","ASB_SUMMARY3");
	    tableNameMap.put("asbSummary8","ASB_SUMMARY8");
	    tableNameMap.put("asbSummary9","ASB_SUMMARY9");
	    tableNameMap.put("asbSummary4","ASB_SUMMARY4");
	}

	private String getInsertFront(String[] names) {
		StringBuffer insert = new StringBuffer();
		insert.append("insert into reporter_status (");
		for (int i = 0; i < names.length; i++) {
			String field = nameMap.get(names[i]).toString();
			String tablename = tableNameMap.get(field).toString();
			if (i != 0) {
				insert.append(",");
			}
			insert.append(tablename);
		}
		insert.append(",type,asb_alarm_seq,serial,serverserial,servername,identifier,asb_nmsems_adapter_id,ownergid");
		insert.append(") values(");
		return insert.toString();
	}

	private String getInsertCentre(String[] names, String[] values, int seq) {
		String severity = "";
		String asbSummary4 = "";
		String asb_location_city = "";
		String ownergid = "";
		String asbNmsEmsName = "";
		String identifier = "identifier:seq=" + seq;
		String adapterId = "";
		String asbAlarmState = "";
		String asbTechDomain = "";
		StringBuffer insert = new StringBuffer();
		for (int i = 0; i < names.length; i++) {
			String xx = values[i];
			String field = nameMap.get(names[i]).toString();
			if ("asbAlarmState".equals(field)) {
				asbAlarmState = values[i];
				xx = transAlarmState(asbAlarmState);
			}
			if ("severity".equals(field)) {
				severity = values[i];
				xx = transClearStatus(severity);
			}
			if ("asbSummary4".equals(field)) {
				asbSummary4 = values[i];
				xx = transSeverity(asbSummary4);
			}
			if ("asbTechDomain".equals(field)) {
				asbTechDomain = values[i];
				xx = transTechDomain(asbTechDomain);
			}
			if ("asb_location_city".equals(field)) {
				asb_location_city = values[i];
			}
			if ("asbNmsEmsName".equals(field)) {
				asbNmsEmsName = values[i];
			}
			int type = Integer.parseInt(columnMap.get(field).toString());
			if (i != 0) {
				insert.append(",");
			}
			if (type == 0) {
				insert.append("'");
				insert.append(xx);
				insert.append("'");
			} else if (type == 1) {
				if (xx.length() == 0){
					xx = "0";
				}
				insert.append(xx);
			} else if (type == 2) {
				if (xx.length() == 0){
					xx = "null";
				} else {
					insert.append("to_date('");
					insert.append(xx);
					insert.append("','yyyy-mm-dd hh24:mi:ss')");
				}
			}
		}
		insert.append(",1");
		insert.append("," + seq);
		insert.append("," + seq);
		insert.append("," + seq);
		insert.append("," + "'AGG_P_OS'");
		insert.append(",'" + identifier + "'");
		insert.append(",'" + transAdapterId(asbNmsEmsName) + "'");
		insert.append("," + transCityCode(asb_location_city));
		return insert.toString();
	}

	private String transTechDomain(String value) {
		if ("固网交换".equals(value)){
			value = "40";
		} else if ("业务网".equals(value)){
			value = "110";
		} else {
			System.out.println("未知专业。。。。。");
		}
		return value;
	}

	private String transAlarmState(String value) {
		if ("重复故障".equals(value)) {
			value = "-1";
		}else if ("未派单".equals(value)) {
			value = "0";
		}else if ("确认".equals(value)) {
			value = "1";
		}else if ("已派单".equals(value)) {
			value = "2";
		}else if ("派单失败".equals(value)) {
			value = "3";
		}else if ("已关闭".equals(value)) {
			value = "25";
		}
		return value;
	}

	private String getInsertBehind() {
		return ");";
	}
	
	private String transSeverity(String value){
		if ("通知".equals(value)) {
			value = "6";
		} else if("紧急告警".equals(value)) {
			value = "5";
		} else if("主要告警".equals(value)) {
			value = "4";
		} else if("次要告警".equals(value)) {
			value = "3";
		} else if("警告告警".equals(value)) {
			value = "2";
		} else if("未知告警".equals(value)) {
			value = "1";
		} else{
			value = "1";
		} 
		return value;
	}
	
	private String transCityCode(String value){
		if ("省中心".equals(value)) {
			value = "100";
		} else if("太原".equals(value)) {
			value = "351";
		} else if("朔州".equals(value)) {
			value = "349";
		} else if("大同".equals(value)) {
			value = "352";
		} else if("忻州".equals(value)) {
			value = "350";
		} else if("阳泉".equals(value)) {
			value = "353";
		} else if("晋中".equals(value)) {
			value = "354";
		} else if("长治".equals(value)) {
			value = "355";
		} else if("晋城".equals(value)) {
			value = "356";
		} else if("临汾".equals(value)) {
			value = "357";
		} else if("吕梁".equals(value)) {
			value = "358";
		} else if("运城".equals(value)) {
			value = "359";
		} else {
			value = "100";
		}
		return value;
	}

	private String transClearStatus(String value){
		if ("已清除".equals(value)) {
			value = "0";
		} else {
			value ="1";
		}
		return value;
	}
	
	private String transAdapterId(String value){
		if ("华为CC08".equals(value)){
			value = "40001";
		} else if ("神州泰岳业务网".equals(value)){
			value = "50004";
		} else {
			System.out.println("未知网管。。。。。");
		}
		return value;
	}
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		int max = 2727637;
		ReadExcel readExcel = new ReadExcel();
		List rows = ExcelTools
				.readExcel("./conf/history.xls", 0);
		System.out.println("rows:" + rows.size());
		String[] names = (String[]) rows.get(0);// 名称
		String insertFront = readExcel.getInsertFront(names);
		String insertBehind = readExcel.getInsertBehind();
		StringBuffer sb = new StringBuffer();
		for (int i = 1; i < rows.size(); i++) {
			String[] cells = (String[]) rows.get(i);
			String insertCentre = readExcel.getInsertCentre(names, cells, max--);
			System.out.println(insertFront + insertCentre + insertBehind);
			sb.append(insertFront + insertCentre + insertBehind + "\n");
		}
		try {
			FileTools.FileOutputStream("./conf/insert.sql", sb.toString());
		} catch (Exception e) {
			e.printStackTrace();
		}
		System.out.println("---THE END---");
	}

}
